﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

using System.Data.SqlClient;
using famails.dto;
using famails.common;

namespace famails.dao
{

    /// <summary>
    /// Summary description for SendContentDAO
    /// </summary>
    public class SendContentDAO
    {
        public SendContentDAO()
        {

        }
        public int tblSendContent_insert(SendContentDTO dt)
        {
            string sql = "INSERT INTO tblSendContent(CreateDate, Subject, Body, UserId, ContentType) " +
                         "VALUES(@CreateDate, @Subject, @Body, @UserId, @ContentType)SELECT SCOPE_IDENTITY()";
            SqlCommand cmd = new SqlCommand(sql, ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = dt.CreateDate;
            cmd.Parameters.Add("@Subject", SqlDbType.NVarChar).Value = dt.Subject;
            cmd.Parameters.Add("@Body", SqlDbType.NVarChar).Value = dt.Body;
            cmd.Parameters.Add("@UserId", SqlDbType.Int).Value = dt.userId;
            cmd.Parameters.Add("@ContentType", SqlDbType.Int).Value = dt.ContentType;
            if (ConnectionData._MyConnection.State == ConnectionState.Closed)
            {
                ConnectionData._MyConnection.Open();
            }
            var contentID = cmd.ExecuteScalar();

            cmd.Dispose();
            return int.Parse(contentID.ToString());
        }
        public void tblSendContent_Update(SendContentDTO dt)
        {
            string sql = "UPDATE tblSendContent SET " +
                        "CreateDate = @CreateDate, " +
                        "Subject = @Subject, " +
                        "UserId = @UserId, " +
                        "ContentType = @ContentType, " +
                        "Body = @Body	WHERE Id = @Id";
            SqlCommand cmd = new SqlCommand(sql, ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@Id", SqlDbType.Int).Value = dt.Id;
            cmd.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = dt.CreateDate;
            cmd.Parameters.Add("@Subject", SqlDbType.NVarChar).Value = dt.Subject;
            cmd.Parameters.Add("@Body", SqlDbType.NVarChar).Value = dt.Body;
            cmd.Parameters.Add("@UserId", SqlDbType.Int).Value = dt.userId;
            cmd.Parameters.Add("@ContentType", SqlDbType.Int).Value = dt.ContentType;
            if (ConnectionData._MyConnection.State == ConnectionState.Closed)
            {
                ConnectionData._MyConnection.Open();
            }
            cmd.ExecuteNonQuery();
            cmd.Dispose();
        }
        public void tblSendContent_Delete(int Id)
        {
            SqlCommand cmd = new SqlCommand("DELETE FROM tblSendContent WHERE Id = @Id",
                ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@Id", SqlDbType.Int).Value = Id;
            if (ConnectionData._MyConnection.State == ConnectionState.Closed)
            {
                ConnectionData._MyConnection.Open();
            }
            cmd.ExecuteNonQuery();
            cmd.Dispose();
        }
        public DataTable GetAll()
        {
            SqlDataAdapter adapter = new SqlDataAdapter("SELECT id, createdate, subject, userid, ContentType FROM tblSendContent",
                ConnectionData._MyConnection);
            DataTable table = new DataTable();
            if (ConnectionData._MyConnection.State == ConnectionState.Closed)
            {
                ConnectionData._MyConnection.Open();
            }
            adapter.Fill(table);
            adapter.Dispose();
            return table;
        }
        public DataTable GetAllWidthBody()
        {
            SqlDataAdapter adapter = new SqlDataAdapter("SELECT id, createdate, subject, userid,Body FROM tblSendContent",
                ConnectionData._MyConnection);
            DataTable table = new DataTable();
            if (ConnectionData._MyConnection.State == ConnectionState.Closed)
            {
                ConnectionData._MyConnection.Open();
            }
            adapter.Fill(table);
            adapter.Dispose();
            return table;
        }

        public DataTable GetAllSendContent(int UserID, Common.contentType contentType)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_GetSendContent";
            cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = UserID;
            cmd.Parameters.Add("@ContentType", SqlDbType.Int).Value = (int)contentType;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            cmd.Connection = ConnectionData._MyConnection;
            if (ConnectionData._MyConnection.State == ConnectionState.Closed)
            {
                ConnectionData._MyConnection.Open();
            }
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }


        public DataTable GetByID(int Id)
        {
            SqlCommand cmd = new SqlCommand("SELECT id, createdate, subject, userid, body FROM tblSendContent WHERE Id = @Id",
                ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@Id", SqlDbType.Int).Value = Id;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            if (ConnectionData._MyConnection.State == ConnectionState.Closed)
            {
                ConnectionData._MyConnection.Open();
            }
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }
        public DataTable GetAll(int userId)
        {
            SqlCommand cmd = new SqlCommand("SELECT * FROM tblSendContent WHERE UserId = @UserId",
                ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@UserId", SqlDbType.Int).Value = userId;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            if (ConnectionData._MyConnection.State == ConnectionState.Closed)
            {
                ConnectionData._MyConnection.Open();
            }
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }

        internal DataTable GetByName(int id, string signatureName, int userid)
        {
            SqlCommand cmd = new SqlCommand("SELECT * FROM tblSendContent WHERE id <> @id and Subject = @sName and userid=@userid",
                ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
            cmd.Parameters.Add("@userid", SqlDbType.Int).Value = userid;
            cmd.Parameters.Add("@sName", SqlDbType.NVarChar).Value = signatureName;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }
    }
}